CREATE OR REPLACE PACKAGE BODY pkg_onboarding AS
/*****************************************************************************
* Name : pkg_onboarding
* Type : Package body
* Description :  For onboarding banks and distributors onto Zapp service
*
* Copyright : VocaLink
*
* Amendment History :
*
* Version  Date         Changed By     Reason (e.g. Initial, Change Ref)
******************************************************************************
* 1.0      26/07/2013   Satyander Thakur        Commom Component
*****************************************************************************/

   NL                           CONSTANT VARCHAR2(1)    := CHR(10);
   v_input_dir                  VARCHAR2(255) := 'APCORE_INPUT_DIR';
   v_output_dir                 VARCHAR2(255) := 'APCORE_OUTPUT_DIR';
   
   
   PROCEDURE load_file_data ( p_file_name          IN VARCHAR2
                            , p_file_id            OUT NUMBER
                            )
   IS
   /*****************************************************************************
    * Name : load_file_data
    * Type : PROCEDURE
    * Description : Procedure to load file raw data into AP database.
    *
    * Parameters In : p_file_name = Name of the file to load
    *                 p_file_id = Unique id assigned to the file using a sequence
    *
    * Parameters Out : None
    *
    * Amendment History :
    *
    * Version  Date         Changed By          Reason (e.g. Initial, Change Ref)
    ******************************************************************************
    * 1.0      26/07/2013   Satyander Thakur    Initial Version
    *****************************************************************************/
      v_procedure_name             VARCHAR2(30 CHAR)  := 'load_file_data';
      v_file_handle                utl_file.file_type;
      v_rec                        VARCHAR2(512);
      v_count                      NUMBER(6);
   
   BEGIN
   
      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Started loading input file: '||p_file_name
                             , p_stage => 'Bank/Distributor file loading'
                             , p_work_identifier => p_file_id
                             );

      v_file_handle := utl_file.fopen (v_input_dir, p_file_name, 'r');
      
      SELECT file_id_seq.NEXTVAL
        INTO p_file_id
        FROM dual;


      v_count := 1;
      IF utl_file.is_open(v_file_handle) THEN
         LOOP

            BEGIN
               utl_file.get_line(v_file_handle, v_rec);
               IF v_rec IS NULL THEN
                  EXIT;
               END IF;
            
               IF v_count = 1 THEN
                  INSERT INTO t_file_header
                            ( file_id
                            , file_name
                            , file_header
                            , load_status
                            )
                       VALUES
                            ( p_file_id
                            , p_file_name
                            , v_rec
                            , 'LOADING'
                            );
               ELSE            
                  INSERT INTO t_file_data
                            ( file_id
                            , record_number
                            , record_data
                            )
                       VALUES
                            ( p_file_id
                            , v_count
                            , v_rec
                            );
               END IF;

            EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                  EXIT;
               END;

            v_count := v_count + 1;

         END LOOP;

      COMMIT;

      END IF;
      
      utl_file.fclose(v_file_handle);

      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Finished loading input file: '||p_file_name
                             , p_stage => 'Bank/Distributor file loading'
                             , p_work_identifier => p_file_id
                             );

   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         
         utl_file.fclose(v_file_handle);
         
         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Error occured while loading file '||p_file_name
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );

         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: loading failed for input file : '||p_file_name
                                , p_stage => 'Bank/Distributor file loading'
                                , p_work_identifier => p_file_id
                                );

         RAISE_APPLICATION_ERROR (-20001, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);

   END load_file_data;

   PROCEDURE populate_bank_table ( p_file_id      IN NUMBER )
   IS
   /*****************************************************************************
    * Name : populate_bank_table
    * Type : PROCEDURE
    * Description : Procedure to load bank records into AP database.
    *
    * Parameters In : p_file_id - Unique identifier of the input data file
    *                
    *
    * Parameters Out : None
    *
    * Amendment History :
    *
    * Version  Date         Changed By          Reason (e.g. Initial, Change Ref)
    ******************************************************************************
    * 1.0      26/07/2013   Satyander Thakur    Initial Version
    *****************************************************************************/
      v_procedure_name             VARCHAR2(30 CHAR)  := 'populate_bank_table';
      v_request_type               VARCHAR2(15);
      v_zapp_service_status        VARCHAR2(15);

      TYPE file_rec_typ  IS RECORD ( record_data   t_file_data.record_data%TYPE );

      TYPE file_rec_tab          IS TABLE OF file_rec_typ;
      file_rec_arr               file_rec_tab;

      CURSOR file_rec_typ_cur IS
      SELECT record_data
        FROM t_file_data
       WHERE file_id = p_file_id
    ORDER BY record_number;
       
   BEGIN
   
      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Bank onboarding: Started populating bank data for input file id: '||p_file_id
                             , p_stage => 'Populate Bank Table'
                             , p_work_identifier => p_file_id
                             );
                             
      OPEN file_rec_typ_cur;
     
      FETCH file_rec_typ_cur
      BULK COLLECT INTO file_rec_arr;
      
      FOR i IN 1 .. file_rec_arr.COUNT
      LOOP
         v_request_type := TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 1));
         IF v_request_type = 'REGISTER' THEN
            v_zapp_service_status := 'PNDGACTIVE';
         END IF;

         INSERT INTO t_financial_institution
                   ( fi_id
                   , fi_identification
                   , fi_name
                   , fi_short_name
                   , holding_account_number
                   , holding_sort_code
                   , holding_roll_number
                   , zapp_service_status
                   , file_id)
              VALUES
                   ( fi_id_seq.NEXTVAL
                   , LPAD(fi_id_seq.CURRVAL,6,'0')
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 2))
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 3))
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 4))
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 5))
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 6))
                   , v_zapp_service_status
                   , p_file_id
                   );
      END LOOP;
      CLOSE file_rec_typ_cur;
   
      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Bank onboarding: Finished populating bank data for input file id: '||p_file_id
                             , p_stage => 'Populate Bank Table'
                             , p_work_identifier => p_file_id
                             );

   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         CLOSE file_rec_typ_cur;
         
         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Bank onboarding: Error occured while populating Bank table for input file id: '||p_file_id
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );

         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: Bank onboarding: Bank data loading failed for input file id : '||p_file_id
                                , p_stage => 'Populate Bank Table'
                                , p_work_identifier => p_file_id
                                );

         RAISE_APPLICATION_ERROR (-20002, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);

   END populate_bank_table;
   
   PROCEDURE create_bank_output( p_file_name          IN VARCHAR2
                               , p_file_id            IN NUMBER
                               )
   IS
   /*****************************************************************************
    * Name : create_bank_output
    * Type : PROCEDURE
    * Description : Procedure to create output file to confirm registration.
    *
    * Parameters In : p_file_name - Name of the output file
    *                 p_file_id - Unique identifier of the input data file
    *
    * Parameters Out : None
    *
    * Amendment History :
    *
    * Version  Date         Changed By          Reason (e.g. Initial, Change Ref)
    ******************************************************************************
    * 1.0      29/07/2013   Satyander Thakur    Initial Version
    *****************************************************************************/
      v_procedure_name          VARCHAR2(30 CHAR)  := 'create_bank_output';
      v_file_handle             utl_file.file_type;
      v_rec                     VARCHAR2(512);


      TYPE bank_rec_typ IS RECORD ( fi_identification       t_financial_institution.fi_identification%TYPE
                                  , fi_name                 t_financial_institution.fi_name%TYPE
                                  , activation_status       t_financial_institution.zapp_service_status%TYPE
                                  , creation_date           t_financial_institution.insert_tmstmp%TYPE
                                  );

      TYPE bank_rec_tab      IS TABLE OF bank_rec_typ;
      bank_rec_arr           bank_rec_tab;
     
      CURSOR bank_rec_typ_cur IS
         SELECT fi_identification
              , fi_name
              , zapp_service_status
              , insert_tmstmp
           FROM t_financial_institution
          WHERE file_id = p_file_id
       ORDER BY fi_id;
           
   BEGIN

      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Bank onboarding: Started creationg bank onboarding response file for input file id: '||p_file_id
                             , p_stage => 'Bank Onboarding Response'
                             , p_work_identifier => p_file_id
                             );

      v_file_handle := utl_file.fopen( v_output_dir, p_file_name, 'w' );
      
      utl_file.put( v_file_handle, 'FI ID, FI Name, Zapp Service Status, Creation Datetime' );
      utl_file.put( v_file_handle, NL );

      OPEN bank_rec_typ_cur;
      
      FETCH bank_rec_typ_cur
      BULK COLLECT INTO bank_rec_arr;
      
      FOR i IN 1 .. bank_rec_arr.COUNT
      LOOP
          v_rec := bank_rec_arr(i).fi_identification
                || ','
                || bank_rec_arr(i).fi_name
                || ','
                || bank_rec_arr(i).activation_status
                || ','
                || TO_CHAR( bank_rec_arr(i).creation_date, 'YYYY/MM/DD HH24:MI:SS' )
                ;

          utl_file.put( v_file_handle, v_rec);
          utl_file.put( v_file_handle, NL);

      END LOOP;

      CLOSE bank_rec_typ_cur;

      utl_file.fclose(v_file_handle);

      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Bank onboarding: Finished creationg bank onboarding response file for input file id: '||p_file_id
                             , p_stage => 'Bank Onboarding Response'
                             , p_work_identifier => p_file_id
                             );

   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         
         CLOSE bank_rec_typ_cur;

         utl_file.fclose(v_file_handle);

         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Bank onboarding: Error occured while creating Bank onboarding response file for input file id: '||p_file_id
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );

         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: Bank onboarding: Response file creation failed for input file id : '||p_file_id
                                , p_stage => 'Bank Onboarding Response'
                                , p_work_identifier => p_file_id
                                );

         RAISE_APPLICATION_ERROR (-20003, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);

   END create_bank_output;

   
   PROCEDURE load_bank ( p_file_name          IN VARCHAR2 )
   IS
   /*****************************************************************************
    * Name : load_bank
    * Type : PROCEDURE
    * Description : Procedure to load bank into AP database.
    *
    * Parameters In : p_file_name - Name of the input data file
    *                
    *
    * Parameters Out : None
    *
    * Amendment History :
    *
    * Version  Date         Changed By         Reason (e.g. Initial, Change Ref)
    ******************************************************************************
    * 1.0      26/07/2013   Satyander Thakur   Initial Version
    *****************************************************************************/
     v_procedure_name           VARCHAR2(30 CHAR)  := 'load_bank';
     v_file_name                VARCHAR2(255);
     v_file_id                  NUMBER(6);
        
   BEGIN
   
      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Bank onboarding Started for input file: '||p_file_name
                             , p_stage => 'Bank Onboarding'
                             );

      load_file_data( p_file_name, v_file_id );

      populate_bank_table ( v_file_id );

      COMMIT;

      v_file_name := 'BANKONBOARDRESP_'||SUBSTR(p_file_name, 13, 11)||'.csv';
  
      create_bank_output( v_file_name, v_file_id );

      UPDATE t_file_header
         SET load_status = 'LOADED'
       WHERE file_id = v_file_id ;
   
      COMMIT;

      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Bank onboarding finished for input file: '||p_file_name
                             , p_stage => 'Bank Onboarding'
                             );

   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         
         UPDATE t_file_header
            SET load_status = 'FAILED'
          WHERE file_id = v_file_id ;
   
         COMMIT;

         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Error occured during onboarding process for the input file : '||p_file_name
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );

         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: Onboarding process failed for input file : '||p_file_name
                                , p_stage => 'Bank Onboarding'
                                , p_work_identifier => v_file_id
                                );

         RAISE_APPLICATION_ERROR (-20004, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);

   END load_bank;

   PROCEDURE populate_distributor_table ( p_file_id     IN NUMBER)
   IS
   /*****************************************************************************
    * Name : populate_distributor_table
    * Type : PROCEDURE
    * Description : Procedure to load distributor records into AP database.
    *
    * Parameters In : p_file_name - Name of the input data file
    *                
    *
    * Parameters Out : None
    *
    * Amendment History :
    *
    * Version  Date         Changed By         Reason (e.g. Initial, Change Ref)
    ******************************************************************************
    * 1.0      26/07/2013   Satyander Thakur   Initial Version
    *****************************************************************************/
      v_procedure_name             VARCHAR2(30 CHAR)  := 'populate_distributor_table';
      v_request_type               VARCHAR2(15);
      activation_status_code        VARCHAR2(15);

      TYPE file_rec_typ  IS RECORD ( record_data   t_file_data.record_data%TYPE );

      TYPE file_rec_tab          IS TABLE OF file_rec_typ;
      file_rec_arr               file_rec_tab;

      CURSOR file_rec_typ_cur IS
      SELECT record_data
        FROM t_file_data
       WHERE file_id = p_file_id
    ORDER BY record_number;
       
   BEGIN
   
      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Distributor onboarding: started populating distributor data for input file id: '||p_file_id
                             , p_stage => 'Distributor onboarding'
                             , p_work_identifier => p_file_id
                             );
                             
      OPEN file_rec_typ_cur;
     
      FETCH file_rec_typ_cur
      BULK COLLECT INTO file_rec_arr;
      
      FOR i IN 1 .. file_rec_arr.COUNT
      LOOP
         v_request_type := trim(regexp_substr(replace(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 2));
         IF v_request_type = 'REGISTER' THEN
            activation_status_code := 'PNDGACTIVE';
         END IF;

         INSERT INTO t_distributor
                   ( distributor_id 
                   , distributor_identification
                   , distributor_name
                   , activation_status_code
                   , settlement_account_number
                   , settlement_sort_code
                   , settlement_roll_number
                   , account_identifier
                   , distributor_category_code
                   , site_name
                   , target_uri
                   , last_update_tmstmp
                   , file_id
                   )
              VALUES
                   ( distributor_id_seq.NEXTVAL
                   , LPAD(distributor_id_seq.CURRVAL,6,'0')
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 1))
                   , activation_status_code
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 3))
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 4))
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 5))
                   , CASE
                        WHEN TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 5)) IS NULL
                        THEN 'SORTCODEACCOUNT'
                        ELSE 'ROLLNUMBER'
                     END
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 6))
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 7))
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 8))
                   , SYSDATE
                   , p_file_id
                   );

         INSERT INTO t_distributor_endpoint_url
                   ( endpoint_url_id
                   , distributor_id 
                   , site_name
                   , endpoint_url
                   )
              VALUES
                   ( device_appintent_id_seq.NEXTVAL
                   , distributor_id_seq.CURRVAL
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 7))
                   , TRIM(regexp_substr(REPLACE(file_rec_arr(i).record_data, ',',', '), '[^,]+', 1, 8))
                   );                   
                   
      END LOOP;

      CLOSE file_rec_typ_cur;
   
      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Distributor onboarding: Finished populating distributor data for input file id: '||p_file_id
                             , p_stage => 'Distributor onboarding'
                             , p_work_identifier => p_file_id
                             );

   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         CLOSE file_rec_typ_cur;

         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Distributor onboarding: Error occured while populating Distributor table for input file id: '||p_file_id
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );

         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: Distributor onboarding: Distributor data loading failed for input file id : '||p_file_id
                                , p_stage => 'Distributor Onboarding'
                                , p_work_identifier => p_file_id
                                );

         RAISE_APPLICATION_ERROR (-20005, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);

   END populate_distributor_table;
   
   PROCEDURE create_distributor_output( p_file_name          IN VARCHAR2
                                      , p_file_id            IN NUMBER
                                      )
   IS
   /*****************************************************************************
    * Name : create_distributor_output
    * Type : PROCEDURE
    * Description : Procedure to create output file to confirm distributor registration.
    *
    * Parameters In : p_file_name - Name of the output file
    *                 p_file_id - Unique identifier of the input data file
    *
    * Parameters Out : None
    *
    * Amendment History :
    *
    * Version  Date         Changed By           Reason (e.g. Initial, Change Ref)
    ******************************************************************************
    * 1.0      29/07/2013   Satyander Thakur     Initial Version
    *****************************************************************************/
      v_procedure_name          VARCHAR2(30 CHAR)  := 'create_distributor_output';
      v_file_handle             utl_file.file_type;
      v_rec                     VARCHAR2(512);


      TYPE dist_rec_typ IS RECORD ( distributor_name                t_distributor.distributor_name%TYPE
                                  , distributor_identification      t_distributor.distributor_identification%TYPE
                                  , activation_status               t_distributor.activation_status_code%TYPE
                                  , creation_date                   t_distributor.insert_tmstmp%TYPE
                                  );

      TYPE dist_rec_tab      IS TABLE OF dist_rec_typ;
      dist_rec_arr           dist_rec_tab;
     
      CURSOR dist_rec_typ_cur IS
         SELECT distributor_name 
              , distributor_identification
              , activation_status_code
              , insert_tmstmp
           FROM t_distributor
          WHERE file_id = p_file_id
       ORDER BY distributor_id;
           
   BEGIN

      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Distributor onboarding: Started creationg Distributor onboarding response file for input file id: '||p_file_id
                             , p_stage => 'Distributor Onboarding Response'
                             , p_work_identifier => p_file_id
                             );

      v_file_handle := utl_file.fopen( v_output_dir, p_file_name, 'w' );
      
      utl_file.put( v_file_handle, 'Distributor Name, Distributor ID, Activation Status, Creation Datetime' );
      utl_file.put( v_file_handle, NL );

      OPEN dist_rec_typ_cur;
      
      FETCH dist_rec_typ_cur
      BULK COLLECT INTO dist_rec_arr;
      
      FOR i IN 1 .. dist_rec_arr.COUNT
      LOOP
          v_rec := dist_rec_arr(i).distributor_name
                || ','
                || dist_rec_arr(i).distributor_identification
                || ','
                || dist_rec_arr(i).activation_status
                || ','
                || TO_CHAR( dist_rec_arr(i).creation_date, 'YYYY/MM/DD HH24:MI:SS' )
                ;

          utl_file.put( v_file_handle, v_rec);
          utl_file.put( v_file_handle, NL);

      END LOOP;

      CLOSE dist_rec_typ_cur;

      utl_file.fclose(v_file_handle);

      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Distributor onboarding: Finished creationg Distributor onboarding response file for input file id: '||p_file_id
                             , p_stage => 'Distributor Onboarding Response'
                             , p_work_identifier => p_file_id
                             );

   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         CLOSE dist_rec_typ_cur;
         utl_file.fclose(v_file_handle);

         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Distributor onboarding: Error occured while creating Distributor onboarding response file for input file id: '||p_file_id
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );

         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: Distributor onboarding: Response file creation failed for input file id : '||p_file_id
                                , p_stage => 'Distributor Onboarding Response'
                                , p_work_identifier => p_file_id
                                );

         RAISE_APPLICATION_ERROR (-20006, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);

   END create_distributor_output;

   PROCEDURE load_distributor ( p_file_name          IN VARCHAR2 )
   IS
   /*****************************************************************************
    * Name : load_distributor
    * Type : PROCEDURE
    * Description : Procedure to load distributor into AP database.
    *
    * Parameters In : p_file_name - Name of the input data file
    *                
    *
    * Parameters Out : None
    *
    * Amendment History :
    *
    * Version  Date         Changed By     Reason (e.g. Initial, Change Ref)
    ******************************************************************************
    * 1.0      26/07/2013   Satyander Thakur        Commom Component
    *****************************************************************************/
     v_procedure_name           VARCHAR2(30 CHAR)  := 'load_distributor';
     v_file_name                VARCHAR2(255);
     v_file_id                  NUMBER(6);
        
   BEGIN
   
      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Distributor onboarding Started for input file: '||p_file_name
                             , p_stage => 'Distributor Onboarding'
                             );

      load_file_data( p_file_name, v_file_id );

      populate_distributor_table( v_file_id );
      
      COMMIT;

      v_file_name := 'DISTONBOARDRESP_'||SUBSTR(p_file_name, 12, 11)||'.csv';
  
      create_distributor_output( v_file_name, v_file_id );

      UPDATE t_file_header
         SET load_status = 'LOADED'
       WHERE file_id = v_file_id ;
   
      COMMIT;

      pkg_logger.log_progress( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_progress_message  => 'Distributor onboarding Finished for input file: '||p_file_name
                             , p_stage => 'Distributor Onboarding'
                             );

   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         
         UPDATE t_file_header
            SET load_status = 'FAILED'
          WHERE file_id = v_file_id ;
   
         COMMIT;
   
         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Error occured during onboarding process for the input file : '||p_file_name
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );

         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: Onboarding process failed for input file : '||p_file_name
                                , p_stage => 'Distributor Onboarding'
                                );

         RAISE_APPLICATION_ERROR (-20007, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);

   END load_distributor;

END;
/